Amazon Redshift + dbt ユーザー必読の書「Best Practices for Leveraging Amazon Redshift and dbt」を読んでみた

データアナリティクス事業本部のコンサルティングチームの石川です。今日は、AWSが執筆した Amazon Redshiftとdbtを活用するためのベストプラクティスをまとめたホワイトペーパーを紹介します。

Best Practices for Leveraging Amazon Redshift and dbt™ リンクから誰でもダウンロードできます。

Redshift + dbtを導入しているけれど、果たしてこれが正しい使い方なのかモヤモヤしている方は少なくないと想像してます。筆者もその一人ですが、そのような方々の道標になれば幸いです。

目次

• Abstract
• Introduction
• Amazon Redshift Deployment Options
• Key Concepts for Understanding dbt
• Optimizing an Amazon Redshift Architecture with dbt
• dbt + Redshift Best Practices
• Performance Tuning your dbt - Amazon Redshift Environment
• Data-Driven Code Optimization in dbt™ and Amazon Redshift
• Conclusion
• Appendix: Recommendations for Setting Permissions and Access Controls in Redshift

Abstract

データの重要性が高まり、従来のオンプレミス環境では解析が困難なため、クラウドへ移行が増えています。Amazon Redshiftでdbtを使いデータ変換を管理し、信頼性のあるデータパイプラインを構築が効果的です。このホワイトペーパーでは、その利点・ベストプラクティスが説明されています。

Introduction

Amazon Redshiftは、使いやすいクラウドデータウェアハウスであり、他のデータウェアハウスソリューションよりもコストが低く、既存のBIツールを使用して効率的にデータ分析ができます。

dbtは、モジュラリティ、CI/CD、埋め込みドキュメントなどのソフトウェアエンジニアリングのベストプラクティスに従って分析コードをデプロイすることで、データ変換を管理が可能です。dbtは、データモデルでSQLのビジネスロジックを実装し、コードベースのテストを自動化し、コードのバージョン管理ができます。

dbtの主な機能

• Amazon RedshiftのDMLとDDL SQLステートメントにプロジェクトコードをコンパイルする
• 正しい順序で変換を実行するための依存関係を推定し、DAG（有向非巡回グラフ）を自動生成する
• マクロとref文により再利用可能なコードを促進する

Amazon Redshift Deployment Options

ここでは、Amazon RedshiftというDWHについて解説します。Amazon Redshiftは、完全マネージド型のサービスであり、プロビジョニングされたオプションとサーバーレス オプションの両方を提供します。

Redshift Serverless

Redshift Serverlessは、インフラのセットアップや管理が不要、分析を数秒で簡単に実行およびスケーリングできます。 自動スケールアップおよび自動スケールダウンします。コストとパフォーマンス要件に基づいて、RPU（Redshift Processing Units）の キャパシティを設定するだけです。

Provisioned Clusters

Provisioned Clustersは、適切なノードタイプと数量を選択し、クラスターをセットアップします。構成、デプロイ、および管理をきめ細かく制御する必要があるワークロードに最適です。

Use cases for Amazon Redshift

• ビジネス・インテリジェンス（BI）
• 機械学習によるイノベーション
• データ共有とコラボレーション
• リアルタイムの運用に関する洞察

さまざまなデータソースからデータ取り込み（data ingestion）、ソースデータの前処理、検証、および ETL（抽出、変換、ロード）などデータ処理（data processing）します。Redshiftにロードしたデータは、データ共有、S3データレイク、RDS、SageMakerなどのデータとブレンディングして、データの利活用（data consumption）します。

Key Concepts for Understanding dbt

dbt Architecture and Benefits

dbtのアーキテクチャと利点は、SQLのselectステートメントによってdbtモデル定義、ビジネスロジックを記述できることです。実行時に、dbtはモデル（model）をコンパイルし、トランザクションの管理、テーブルの削除、およびスキーマの変更の管理に必要な DMLやDDLを処理します。コードは再利用可能でモジュール化されており、特にマクロ（macro）、フック（hook）、パッケージ管理（package）を使用して、後続の dbtモデルで参照できます。

dbtは、無料で使えるオープンソースのdbt coreの他に、dbt CloudというSaaSを展開、 Web ベースのユーザーインターフェイス (UI) 内で、ジョブスケジューリング、CI/CD 統合、ドキュメントの提供、ネイティブgit統合、監視とアラート、および統合開発環境 (IDE) のターンキー サポートを提供します。

Documentation and Dependency Handling

dbtは、別のモデルのSelectステートメントで常にref関数を使用することで、(1)依存関係を推測し、(2)環境に基づいて依存関係の正しい上流のテーブルとビューが選択されます。

実行のスレッド数を増やすと、dbtは同時に処理できるグラフ内のパスの数を増やし、プロジェクトの実行時間を短縮します。

dbtプロジェクトのノードの例として、モデル（model）、ソース（source）、テスト（test）、スナップショット（snapshots）が挙げられます。スレッドとノード選択を組み合わせることで、モデルの構築順序を明示的に宣言することなく、必要なものだけを実行することでdbtの実行を最適化できます。つまり、前回の実行から変更されたモデルに基づいて実行したり、並行してモデルを実行しながら失敗から再スタートしたりすることができます。

dbtのDAGの例：モデルdim_suppliersでstg_tpch_nations、stg_tpch_regions、stg_tpch_suppliersへの参照を宣言して、dbtの実行時に3つのスレッドにすると、dbtはdim_suppliersを実行する前に、最大3つのモデルを同時に処理します。

ソースは refsと同様に動作しますが、重要な違いは、モデルが他のモデルにどのように関連しているかを dbt に伝えるのではなく、モデルがソースオブジェクトにどのように関連しているかを dbt に伝えるという点です。このようにモデルからソースへの依存関係を宣言することで、2つの重要なことが可能になります。モデルでソースのテーブルを選択できるようになり、ソースデータを含むより広範なプロジェクトのテストや文書化を行うことができるようになります。

Making Code Modular Using Macros

dbtでは、テンプレート言語であるJinjaを用いて、制御構造の使用や環境変数の設定が可能です。Jinjaで書かれたコードのうち、dbtプロジェクト全体で再利用可能なものをマクロと呼びます。マクロは、他のプログラミング言語における関数に類似しており、1つの中心的な場所でコードを定義し、他の場所でそれを再利用することができます。前述したref関数やsource関数はJinjaの例です。マクロは、環境ロジックに役立つだけでなく、GrantステートメントのようなRedshiftの管理タスクを運用したり、非推奨オブジェクトをシステム的に削除したりするのに役立ちます。基本的に何度も書いたり実行したりするようなコードは、マクロに入れることを検討すべきです。

Jumpstarting dbt development with Packages

dbt packagesは、特定の目的やデータソースに対応したオープンソースのモデルやマクロを集めたライブラリです。

一般的なパッケージ：

• Facebook AdsやNetsuiteなど、一般的なデータソースのモデリング手法
• dbtの標準テストから外れる便利なテスト
• dbtの変換をdbt_mlのような新しい領域に押し上げるための革新的な成果
• And more!

dbt Labsは現在、Redshiftシステムテーブルのための多くのモデル、そしてテーブルの圧縮、テーブルのアンロード、さらにはテーブルのバキュームと分析を助けるいくつかの便利なマクロを提供するProvisioned ClusterのためのRedshiftパッケージをサポートしています。これらは、自分で明示的にモデル化することなく、Redshiftのクエリや権限データにアクセスして分析したい場合に、非常に便利です。

ユーザーは、dbtプロジェクトのpackages.ymlファイルに必要なパッケージ名とバージョンを追加して、dbt depsを実行するとインストールされ、通常のrefやマクロの構文を使用して、プロジェクト内のマクロやモデルにアクセスできるようになります。dbtパッケージハブにないプライベート、ローカル、またはgitパッケージについては、dbtプロジェクトへのパッケージのインストールについて、こちらで詳しく説明しています。

dbt_utilsパッケージは、dbtプロジェクトに追加し、データモデリングにおけるDRY（Don't Repeat Yourself）原則を守るためのマクロを利用します。dbt_utilsには、データモデリングにおける共通の問題点を軽減するための一連のマクロとテストがあります。

dbt_utilsパッケージは、Redshiftのinsert_by_periodマテリアライゼーションタイプもサポートしており、dbtは一度に1つの期間（つまり、日、週）のテーブルにレコードを挿入することができます。このマテリアライゼーションは、期間を区切って処理できるイベントデータや、特定のセクションで失敗するような信じられないほど大きなデータロードがある場合に最も有用です。

Documentation as you code with dbt

dbtは、データソース、モデル、テスト、後工程（BIやアプリケーションなど）に公開するのための堅牢なドキュメントを作成できます。dbt docsは、ビジネスユーザーを含む組織内のすべての人が、データセットや変換ロジックに関する日常的な質問に答えるのに役立ちます。

dbtでは、dbtプロジェクトのオブジェクトの明確な所有権を明確にするため、ソース、モデル、テスト、マクロのメタフィールドの活用も推奨しています。メタフィールドは、モデルの所有者、成熟度、PII（個人情報）のような機密データを含む場合のフラグを明確に識別するための素晴らしい方法です。

すべてのdbtのドキュメントは、2つのCLIコマンド（dbt docs generate && dbt docs serve）を実行した後にローカルホストのDocumentationセクションから参照できます。

Resources for Learning More

より多くのことを学ぶために、さまざまなリソースが公開されています。

• dbtを初めて使う方：Quickstart for dbt Cloud and Redshift
• Amazon Redshiftでdbt CLIを使い始めたい方：dbt CLI and Amazon Redshift

Optimizing an Amazon Redshift Architecture with dbt

ここでは、dbtによるAmazon Redshiftアーキテクチャの最適化について解説します。

dbtを導入するとETL（Extract-Transform-Load）フレームワークの代わりに、ELT（Extract-Load-Transform）フレームワークを使用すると、データ変換（Transform）をRedshift内部で処理するようになります。ELTフレームワークへの移行により、組織内の幅広い人々がデータ変換に貢献できるようになります。その中には、深いビジネスコンテキストを持っていてもSQL以上のプログラミングスキルを持たない実務者も含まれます。

Connecting dbt to Amazon Redshift

dbtフレームワークは、dbtの機能を所定のデータプラットフォームに「適応」させるためにアダプタを使用します。dbtRedshiftアダプタは、dbtがRedshiftに接続することを可能にします。接続するとdbtはRedshiftに準拠したSQLをRedshiftに発行して実行し、モデルファイルを永続化オブジェクトに変換できるようになります。

dbt CoreをRedshiftで使用するには、dbt-redshiftアダプタをローカルにインストールした後、ツールセット（テキストエディタ、コマンドラインインターフェイスなど）で設定すると、Redshiftでdbtを使用する準備は完了です。

dbt Cloudでは、インストールは必要ありません。dbt Cloudは完全にブラウザベースのインターフェースであるため、環境の維持やソフトウェアのアップデートにかかる時間を節約することができます。

dbt + Redshift Best Practices

Choose A Redshift Cluster or Serverless Endpoint

Redshift RA3ノードタイプとサーバーレスエンドポイントは、Redshift Managed Storage（RMS）を活用することで、コンピュートとストレージを分離します。データ共有、スケーラブルなコンピュートとストレージ、AWS Data Exchange、クロスデータベースクエリなどの機能とともに、優れた性能を提供します。

• Provisioned Clusterの場合：RA3ノードタイプが推奨です。
• Redshift Serverlessの場合：ユーザーがサイジングを気にする必要がなく、自動的に一時停止と再開するため、従量課金モデルという利点もあります。ほぼゼロ管理のクラウドデータウェアハウスを期待するのであれば、Redshift Serverlessは正しい選択です。

Plan for Quality Assurance

dbtとRedshiftでは、本番環境と開発環境を分離して設定することが重要です。これにより、テストされていないデータをビジネスインテリジェンス（BI）プラットフォームに公開するリスクを低減し、コストのかかる再構築の必要性を制限することができます。また、QAフレームワークを作成することも重要です。

• 開発環境と本番環境で同じ生データにアクセスできるチームの場合
  • データが同じであるため、本番環境に移行する前に開発環境でQA関連タスクを実行できます
• 開発環境と本番環境とで異なるバージョンの生データにアクセスするチームの場合
  • 開発環境と本番環境とは別のQA環境を用意する方が良いかもしれません
  • 特に、開発環境とは対照的に、QA環境で新しい承認者を導入する場合は、この方法が良いです

私たちは、コードを開発環境から本番環境へ移行させるために、QAワークフローを常に推奨していますが、実際にどこでチェックを実行するかは関係ありません。この記事では、開発環境の中でQAワークフローを管理するという、よりシンプルなアプローチを紹介します。

Configure your Data Warehouse Environment (Multiple Redshift Clusters)

raw、dev、prodという名前の3つ環境を用意する場合、以下の2つのアプローチがあります。

• 別々のRedshiftクラスターまたはサーバーレスエンドポイントを3つ作成する場合
  • データ共有を有効にする必要がある（Redshift RA3およびServerlessでのみ使用可能）

  • データ共有は、クラスタ間でデータをコピーまたは移動することなく、別のクラスターとデータセットを共有が可能

• RA3インスタンスまたはサーバーレスを使用して1つのクラスタまたはエンドポイントに3つのデータベースを作成する場合
  • クロスデータベースクエリを利用する

Raw Database

dbtモデルで使用する生データの重複を避けるため、生データは開発環境と本番環境の両方からアクセスできる別のデータベースに格納することを推奨します。ただし、開発環境に公開できないPHI（保護された健康情報）やPII（個人情報）のデータがある場合は除きます。このような場合は、PHI/PIIデータをprodに置き、クリーン化されたバージョンの生データをdevデータベースに置くことを推奨します。この場合の目標は、データセキュリティの要件を満たしつつ、重複するデータを最小限に抑えることです。

生データベースが作成され、データをロードする準備ができたら、各データソースからモデル化されていないデータのためのrawスキーマを作成します。未加工のソースデータについては、スキーマオブジェクトにそのデータをロードしたELTツールのプレフィックスを付けることをお勧めします

例：airbyte_facebook_ads

Dev Database

dbt 開発者が自分の名前（例：dbt_amyc）をプレフィックスとした、開発用のサンドボックス/開発スキーマを持つことを推奨します。このスペースでは、dbt開発者は、他の開発スペースに影響を与えるリスクなしに、モデルの作成、更新、削除できます。

Prod Database

prodデータベースでは、プロダクションデータオブジェクトを機能別または垂直方向に整理することを推奨します。これを行うには、dbtモデルを異なるプロダクションスキーマに入れることができます。カスタムデータベース/スキーマを使用することをお勧めします。

例えば、プロダクション・マーケティング・モデルを保持するスキーマをマーケティングと呼ぶことになります。複数のチームで必要とされる中核的なモデルであれば、スキーマをcoreと名付けることができます。

Configure your Data Warehouse Environment (Single Redshift Cluster or endpoint)

Redshift Clusterやエンドポイントを1つだけ導入し、すべての環境のホストに使用する場合：

• 3つのスキーマを持つRedshift DC2
• 複数のデータベースを持つRA3ノード
• 複数のデータベースを持つサーバーレスエンドポイント

例えば、3つのスキーマを持つRedshiftの場合、環境間の組織的な仕切りとしてスキーマを持つ1つのデータベースを作成し、例えば、rawスキーマにはraw_、開発スキーマにはdbt_とします。関数に基づく本番用スキーマには、本番用スキーマであることを示すprod_ステートメントを付加してください。

例えば、プロダクションマーケティングモデルを保持するスキーマをprod_marketingと呼びます。

Set up Role Based Access Controls (RBAC), if necessary

Amazon Redshiftのデータベース権限を管理するためにロールベースアクセスコントロール（RBAC）を使用することで、異なる権限を異なるロールに割り当て、これらのロールを異なるユーザーに割り当てることで、ユーザーアクセスをより詳細に制御することができます。RBACは行レベル、列レベルのセキュリティをサポートします。

Use Redshift’s Late Binding Views for Downstream Consolidation

レイトバインディングビューは、上流のビューやテーブルがcascadeでDropされた場合でも、Dropされません。レイトバインディングビューは、テーブルをスワップする必要がある場合に便利です。

dbtプロジェクトでレイトバインディング・ビューを物理化または実体化するには、モデルまたはプロジェクト・レベルでdbtの設定を指定します。モデルファイルでは、次のようなconfigブロックを使用して、レイトバインディングビューのマテリアライズを明示的に設定することができます：

{{ config(materialized=’view’, bind=False) }}

また、プロジェクトやサブフォルダレベルで設定を行いたい場合もあります。その場合は、dbt_project.ymlファイルにbind変数を追加（階層を変更）してください。

name: ‘my_dbt_project’ 
version: 1.0 
…
#This will make all view models in your project use the late-binding 
#modifier. The `bind` configuration can be specified anywhere in the 
#configuration hierarchy 
models: 
  bind: false

Performance Tuning your dbt - Amazon Redshift Environment

dbtとRedshiftには、モデルをより高性能に、よりコスト効率よくするための戦略があります。

Using sort and distribution keys (on large tables)

ソートキーと分散キー（dist）を正しく使用すれば、ビルド時間とコストを劇的に改善することができます。dbtのモジュラーモデリングの考え方とアクセス可能な構成と組み合わせることで、ソートキーと分散キーの使用は、モデル構成ブロックに数行を追加するだけでよいのです。

ソートキー

ソートキーは、テーブルのレコードの格納順を決定し、それに応じてテーブルをソートします。処理中に最小値や最大値のようなメタデータが生成され、Redshiftはクエリ実行中にデータを反復することなくこれらの値にアクセスすることができます。複合ソートキーは、述語がソートキーのカラムのサブセットであるプレフィックスを順番に使用する場合に、より効率的です。インターリーブド・ソートキーは、ソートキーの各カラムに同じ重みを与えます。

分配キー

分配キーは、指定した列の値に従ってコンピュートノードに分配されます。結合キーでテーブルのペアを配布する場合、Redshiftは結合カラムの値に従ってコンピュートノードにレコードをコロケーションします。こうすることで、共通の列からの一致する値が物理的に同じコンピュートノードのスライスに保存されます。分散スタイルには、auto、all、even、keyの4種類があります。分散スタイルを指定しない場合、Amazon Redshiftはautoを使用し、Redshiftがテーブルサイズに基づいたスタイルを割り当てることを意味します。

自動テーブル最適化（ATO）は、管理者の介入なしにソートおよび分散スタイルを適用してテーブル設計を最適化するRedshiftの自己チューニング機能です。ATOを有効にするには、DiststyleをAutoに設定し、ソートキーをautoに設定した新しいテーブルを作成します。また、既存のテーブルをalter tableコマンドで変更し、分配スタイルとソートキーをautoに変更することも可能です。

オートスタイルを推奨

分散スタイルとソートキーは、よりマニュアル的な配信設定が必要になるまで、オートスタイルを推奨します。始める前に、モデルのパフォーマンスに関するデータを見て、dbtプロジェクトのDAG（有向非循環グラフ）と起こっている変換について明確に理解するようにしてください。

EXPLAINプランを使って、長く実行されているモデルのCTEを分解し、ロジックをクリーンアップしたり、別のモデルに抽出したりできるかどうかをチェックします。最終的には、最も問題のある結合を減らし、ソートと分散キーを調整することでベンチマークを行うことをお勧めします。ほとんどの場合、自動化された方法以外では、最も大きいテーブルでカーディナリティの高いカラムに分散キーを使用することをお勧めします。

Storing data in an efficient manner

データをより効率的に保存し、メモリ使用量とディスク使用量を最小限に抑え、クエリ性能を向上させるために、以下のテーブル設計の実践を推奨します：

• 圧縮は列レベルの操作で、データの保存時にサイズを縮小し、ディスクI/Oを減らすことでクエリ性能を向上させる
  • 通常は自動圧縮が最良の結果となる事が多い、テーブルのデフォルトはENCODE AUTO
• VARCHAR カラムに格納する可能性のある最大の値を考慮する
• 数値と日付・時刻を対応するデータ型として保存する

Configuring Amazon Redshift workload management (WLM)

Provisioned Clustersでは、ワークロードの種類ごとに個別のWLMキューを作成することによって、Redshiftの自動ワークロード管理（Auto WLM）機能を利用します。Auto WLMは、各ワークロードの実行時間に基づいて同時実行レベルを適応させることで、リソース使用を最適化します。これにより、短時間で高速に実行されるクエリが、長時間実行されるクエリの後ろでキューに滞留することがなくなります。

また、WLMキューを分離することで、WLMクエリモニタリングルール（QMR）を使用して、より細かい粒度でクエリの優先順位を制御することができます。一度設定されたルールは、キュー待ち時間、実行時間、CPU使用率などのメトリクスに基づいて、実行時にクエリを昇格または降格させます。

Choosing Appropriate Model Materializations

dbtは、ビュー、テーブル、エフェメラル、インクリメンタルの4つのマテリアライゼーション戦略をサポートしています。デフォルトでは、dbt_project.ymlやモデル設定ブロックで定義されていない限り、dbtはモデルをビューとして実体化します。

dbtプロジェクトの強固な基盤を作るためにビューやテーブルから始め、必要に応じてより複雑なアプローチ（インクリメンタルモデルなど）で効率化を図ることをよくお勧めします。

インクリメンタルモデルは、最初の実行時には通常のテーブルと同じように構築されます。その後の実行では、インクリメンタルフィルターの条件に合致するデータ（通常、前回の実行以降にソーステーブルに追加または更新されたデータ）だけを処理し、最初の実行で構築したベーステーブルに変更内容を挿入します。1回の実行で変換するデータ量を制限することで、インクリメンタルモデルは変換のランタイムとコストを削減することができます。インクリメンタルマテリアライゼーションは、ウェブサイトのイベントデータや電子メールCRMプラットフォームのデータなど、大容量のデータセットに適用されることが多いようです。

上級者は、独自のカスタムマテリアライゼーションを作成することにも興味があるかもしれません。カスタムマテリアライゼーションは、他のマテリアライゼーションと同様に、dbtプロジェクト全体で参照することができます。

Materialized Views in Redshift

マテリアライズド・ビューは事前に計算された結果セットを含み、複数の結合や集計を含むような複雑なクエリを高速化するために使用することができます。Redshift でマテリアライズド・ビューを作成するには、materialization macrosで必要なデータ定義言語 (DDL) を使用します。

Limiting the amount of data used during development periods

モデルを開発する際、データセット全体に適用する前にロジックを微調整する必要があることがよくあります。条件付きロジックを使えば、この段階でデータの使用量を制限してコストを削減することができ、LIMIT句やWHERE句を手動で記述して削除する必要がありません。

DRY（繰り返さない）な方法で条件付きロジックを適用するには、プロジェクト全体のモデルで呼び出すことができるマクロを使用します。以下のサンプルコードでは、ターゲットがdevに設定されているときにWHERE句を追加しています：

Using dbt packages to keep projects DRY

dbt_utilsパッケージを使用すると、データモデルにDRY（繰り返さない）原則を適用することができます。データモデリングでよくある問題を解決するためのマクロとテストが含まれており、Date Spineの作成、カラムのピボット解除などのタスクを迅速に行うことができます。

dbt_utilsは、insert_by_periodという実体化タイプをサポートしており、dbtは一度に1つの期間（例えば、日、週）のテーブルにレコードを挿入することができます。このマテリアライゼーションは、期間を区切って処理できるイベントデータや、特定のセクションで失敗する大規模なデータロードに最も有効です。

チームやデータの増加に伴い、複数のdbtリポジトリでロジックや定義を標準化するために、内部パッケージを作成し共有することが考えられます。これにより、冗長なコードを制限し、ビジネスロジックとメトリック定義の一貫性を保つことができます。

Leveraging Redshift Spectrum for large datasets.

Redshift Spectrumを使用すると、S3に保存されているデータを直接クエリできます。

• より多くのストレージが必要だが、より多くのコンピュートが必要でない場合、Amazon Redshiftで頻繁にクエリされないデータをS3にオフロードし、Redshift Spectrumを使用すれば、コストを削減できます
• Parquet, Avroなどを含むオープンデータ形式をS3で直接クエリできるため、Redshift Spectrumを使うことは有益です

Redshift Spectrumは、より意味のある洞察を得るために集約する必要があるWebやメールのイベントデータなど、1日あたり数十億行の大規模データセットに適している。このようなデータでdbtを使用する前に、以下のベストプラクティスに従ってください：

1. SQLの述語で頻繁に使用されるカラム（例えばevent_date）に基づいてS3プレフィックスを定義し、パーティション化する
   • 複数の述語を頻繁に使用する場合は、マルチレベルパーティション化を推奨
2. テーブル統計（numRows）を設定します。

Amazon Redshiftで外部テーブルを作成・管理するには、dbtのexternal tables packageを使用します。この機能により、S3に保存されたファイルや外部データソースからYAMLで定義されたソースを作成することができます、その後、dbt run-operationコマンドを実行し、ソースの作成、更新、およびドロップを行います。

Redshift Spectrumでdbtのexternal tables packageを使用する

• 既存のS3バケット
• テーブルを作成するためのパーミッション
• AWS Glue Data Catalogの外部データベースとRedshiftの外部スキーマで、dbtがRedshiftの外部テーブルを作成する

Redshift Spectrumでクローラーを使用する

1. AWS Glue Data Catalogデータベースを参照する外部スキーマを作成する。
2. Data Catalogデータベースの外部テーブルを作成するクローラーを作成・実行する。

クローラーによって作成された外部テーブルへの依存関係をdbtに認識させるには、dbtプロジェクトで外部テーブルをソースとして定義します。

Amazon Redshift best practices for performance tuning: Blog

下記のブログを合わせてご覧ください。

Data-Driven Code Optimization in dbt and Amazon Redshift

dbtが生成するメタデータとRedshiftのEXPLAINコマンドを活用することで、コードやモデルのデバッグや最適化を体系的に行うことができます。

Analyzing dbt Metadata

dbt Cloudは、ジョブを実行するたびに、dbtプロジェクト内のモデルのタイミング、構成、および鮮度に関するメタデータを生成します。dbt Metadata API は GraphQL サービスで、グラフィカルエクスプローラーやエンドポイントからこのメタデータに対するクエリをサポートします。

Using Redshift’s EXPLAIN and Query Summary

RedshiftのEXPLAINコマンドを実行して、クエリオプティマイザでモデルがどのように実行されるかを確認できます。与えられたモデルの完全なクエリプランを見ることで、以下の情報を得ることができます：

• 実行エンジンが行う操作
• 操作ごとに使用するテーブルとフィールド
• 各ステップで処理されるデータ量
• 運用コスト

EXPLAINコマンドは実際にクエリを計算するわけではないことに注意してください。

Conclusion

Amazon Redshiftとdbtは連携して、データウェアハウス、およびトランスフォーメーション業務を強化します。Amazon Redshiftは、サーバーレス、データ共有、Redshift Spectrum、Federated queryなどの高度な機能を提供し、データの取り込みとクエリを最適化します。一方、dbtは、モジュール化、CI/CD、SQLベースのロジックといったソフトウェアエンジニアリングのベストプラクティスを通じて、効率的なデータ変換を可能にします。Amazon Redshiftとdbtを組み合わせることで、シームレスな信頼性の高いデータパイプラインを実現し、データから迅速な洞察を得ることが可能になります。

Appendix: Recommendations for Setting Permissions and Access Controls in Redshift

下記の権限とグループをセットアップするための推奨事項をサンプルを解説しています。

Setting Up Group Permissions

Set up Groups

Redshiftでは、グループとは、同じ権限を共有するユーザーの集まりです。まずは、loader、transformer、reporterの3つの主要なグループから始めることをお勧めします。

• loaderグループは、データをデータベースにロードします
  • FivetranやAirbyteのようなExtract and Load（EL）ツールは、このグループに属する
• transformerグループは、データを変換します
  • このグループには、dbtの開発者やdbtクラウドのようなアプリケーションが含まれる可能性がある
• reporterグループは、データベース内のデータを読み込みます
  • このグループには、warehouseのモデルを照会する必要があるMLツールが含まれるかもしれない

各グループを確立するには、次のコマンドを実行します： create group group_name.

Create Users and Allocate to Groups

次にユーザーを作成し、グループに割り当てます。各人およびwarehouseのデータをロード、変換、または読み込む必要のある各アプリケーション（ELツール、BIプラットフォーム、またはdbtクラウドなど）ごとに、データベースユーザーアカウントを作成する必要があります。

ETL ツールは一般的にローダーとして分類されますが、dbt クラウドは dbt プロジェクトからスケジュールされた本番ジョブを実行するためにトランスフォーマーの権限を必要とします。

以下のコード例では、ユーザーを作成し、グループに割り当てています：

create user your_data_loader
    password '_generate_this_'
    in group loader;
create user your_user_1
    password '_generate_this_'
    in group transformer;
create user dbt_cloud
    password '_generate_this_'
    in group transformer;
create user your_bi_tool
    password '_generate_this_'
    in group reporter;

次に、各グループに適切な権限を明示的に付与する必要があります。

Loader Group Permissions

loaderグループには、スキーマとスキーマ・オブジェクトを作成する権限が必要です。スキーマを作成する権限があれば、そのグループはそのrawスキーマでテーブルとビューを作成することもできるようになります。loaderグループにELTツールが含まれている場合、そのグループに情報スキーマに関する何らかの権限があることを確認してください。

以下の行を実行して、loaderグループに正しい権限を付与してください：

grant create on database analytics to group loader;
grant select on all tables in schema information_schema to group loader;
grant select on all tables in schema pg_catalog to group loader;

Transformer Group Permissions

transformerグループのユーザーがdbtモデルの作成、dbtドキュメントの生成、分析を行えるようにするためには、以下の権限を付与する必要があります：

• スキーマ使用へのアクセス
• スキーマオブジェクトの選択特権
• 将来のスキーマオブジェクトのための選択特権
• 情報スキーマの選択特権

スキーマの利用権を付与するには、各スキーマに対して以下のコマンドを実行します：

grant usage on schema my_schema to group transformer;
grant select on all tables in schema my_schema to group transformer;
alter default privileges for user my_user in schema my_user
grant select on tables to group transformer;

FivetranやAirbyteのようなELTツールを使ってデータベースに新しいスキーマを追加するたびに、これらのコマンドを再実行する必要があります。

データベース内のオブジェクトを作成するためのアクセス権を付与するには、次のように実行する:

grant create on database analytics to group transformer;

情報スキーマにselect権限を付与するには、次のように実行する:

grant select on all tables in schema information_schema to group transformer;
grant select on all tables in schema pg_catalog to group transformer;

Reporter Group Permissions

テーブルとビューがtransformerロールで作成された後、reporterグループに読み取りアクセス権を付与することができます。このグループには、BIやMLツールなど、下流でこれらのオブジェクトを照会する必要があるツールが含まれる場合があります。

テーブルとビューへのアクセスを管理する最良の方法は、dbtのgrants設定を使用して、これらの権限をdbtで直接設定することです。YAMLファイル内のmodels configブロック、またはモデルのSQLファイル上のconfig Jinjaマクロで設定を適用することができます。

dbt_project.ymlでは、このような設定になります：

models:
  +grants:
    select: ['reporter']

モデルファイル上では、以下のようになります：

{{ config(grants = {'select': ['reporter']}) }}

注意：複数の場所（dbt_project.yml、resources.yml、モデルのSQLファイル）でグラントを設定した場合、dbtはより特定的なグラントを、より特定的ではないグラントより優先します。例えば、モデルの YAML ファイルとモデルの SQL ファイルに異なるグラントが設定されている場合、dbt は SQL ファイルに適用されたグラントのみを適用します。既存のグループに加えて新しいグループを適用することを示すために、+（追加）側を適用することができます。

これは次のようになります：

{{ config(grants = {'+select': ['reporter']}) }}

Authentication Methods

dbtは、Redshiftユーザーの認証方法として、パスワードベースのシンプルなものを含む複数の認証方法をサポートしています。認証、IAM認証、dbtクラウドのSSHトンネルによる接続が可能です。

Username and Password Authentication

ローカルで開発している場合は、ローカルのprofile.ymlを調整して、明示的に接続を設定してください。 ユーザー名とパスワードを使用するターゲットです：

company-name:
  target: dev
  outputs:
dev:
  type: redshift
  host: hostname.region.redshift.amazonaws.com
  user: username
  password: password1
  port: 5439
  dbname: analytics
  schema: analytics
  threads: 4
  keepalives_idle: 240 # default 240 seconds
  connect_timeout: 10 # default 10 seconds
  sslmode: [optional, set the sslmode used to connect to the database (in case this parameter is set, will look for ca in ~/.postgresql/root.crt)]

Secrets Managerを使用している場合は、パスワードを直接入力するのではなく、環境変数があり、下のような形になります：

Password: "{{ env_var('DBT_USER_PASSWORD') }}"

Authentication with IAM

ローカル開発用にIAM認証を使用したRedshiftプロファイルを設定するには、ローカルのprofiles.ymlファイルでmethodパラメータをiamに設定します。IAM Authenticationを使用する場合、パスワードは必要ないことに注意してください。 ローカルのprofiles.ymlは以下のような感じになります：

my-redshift-db:
  target: dev
  outputs:
    dev:
      type: redshift
      method: iam
      cluster_id: [cluster_id]
      host: hostname.region.redshift.amazonaws.com
      user: username
      iam_profile: data_engineer # optional
      iam_duration_seconds: 900 # optional
      autocreate: true # optional
      db_groups: ['analysts'] # optional
      # Other Redshift configs:
      port: 5439
      dbname: analytics
      schema: analytics
      threads: 4
      keepalives_idle: 240 # default 240 seconds
      sslmode: [optional, set the sslmode used to connect to the database (in case this parameter is set, will look for ca in ~/.postgresql/root.crt)]
      ra3_node: true # enables cross-database sources

すべての潜在的なprofile.ymlの構成については、このドキュメントを確認してください。 ** IAM Authenticationは、公開時点（2023/03時点）ではRedshift Serverlessではサポートされていません。

SSH Tunnel Connection

dbt Cloudを使用している場合、SSHトンネルを使用してdbtプロジェクトをRedshiftに接続することができます。dbt Cloudの「Projects」セクションに移動し、以下のステップを実行します：

1. ホスト名、ポート（Redshiftでは通常5439）、ユーザー名を追加します
2. 接続を保存すると、公開鍵が生成され、「公開鍵」セクションに表示されます
3. このキーをbastionサーバーにコピーして、dbt CloudがRedshiftに接続することを承認する

Protecting PII: Dynamic Data Masking with dbt

データウェアハウスでは、特定のユーザーだけが本当のカラム値を閲覧できるようにしたい場合がよくあります。非表示にしたいカラムには、管理者だけが閲覧・アクセスできるような機密性の高い個人情報が含まれていることがよくあります。特定のユーザーに対してこのデータを隠したり歪めたりするには、dbtマクロを作成して、データモデル間で使用できるデータマスキングロジックを確立することができます。このマクロの核心は、Redshiftのcurrent_user()関数の値を使用して、本当のカラム値またはカラムのハッシュ化された値のどちらを表示するかを決定します。Redshiftでこのタイプのデータマスキングを実装するための深い掘り下げは、この談話とこのGithubリポジトリで見つけることができます。Amazon Redshiftは現在、動的なデータマスキングをネイティブでサポートしています。

最後に

Redshift + dbtのベテランの方にとっては答え合わせ、初学者の方は導入検討資料として活用していただける内容だったのではないでしょうか。大きな発見はありませんでしたがモヤモヤ解消した気がします。

SQLという宣言型の言語を、dbtという宣言型フレームワークでラップし、ref関数を用いてモデルの依存関係を定義することで、DAGの自動生成を可能にしています。dbtにおいてはDAGがデータ変換の実行プランであり、データリネージでもあります。スレッド数に応じて同時に処理できるグラフ内のパスの数を増やし、プロジェクトの実行時間を短縮、Redshift Serverlessにおいてもコンピューティングリソースを余すところなく有効活用できます。個人的には、Redshiftのdbtvaultパッケージのリリースが待ち遠しいです。